package com.example.day05.dao;

import com.example.day05.pojos.Blog;
import com.example.day05.vo.BlogVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Component
@Slf4j
public class BlogMapper {

    public boolean add(Blog blog) {
        //实现对数据库的写入
        try {
            //加载驱动-jdbc
            Class.forName("com.mysql.cj.jdbc.Driver");
            // url?
            //连接数据库
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/blog", "root", "root");
            //执行sql语句
            Statement statement = conn.createStatement();
            String sql = "insert into `blog`(`title`, `author`, `content`, `view_count`, `category_id`, `status`)" +
                    "values ('#title', '#author', '#content', #view_count, #category_id, '#status')";
            sql = sql.replace("#title", blog.getTitle());
            sql = sql.replace("#author", blog.getAuthor());
            sql = sql.replace("#view_count", blog.getViewCount() + "");
            sql = sql.replace("#content", blog.getContent());
            sql = sql.replace("#category_id", blog.getCategoryId() + "");
            sql = sql.replace("#status", blog.getStatus());
            log.info("sql:{}", sql);
            boolean result = statement.execute(sql);
            conn.close();
            log.info("{}", result);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //接收处理结果
        return false;
    }

    public BlogVO findById(int id) {
        BlogVO blogVO = new BlogVO();

        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/blog", "root", "root");
            //执行
            Statement statement = conn.createStatement();
            String sql = "select blog.id,title,author,content,view_count,name,status from blog left join category  on category.id = blog.category_id where blog.id=" + id;
            ResultSet resultSet = statement.executeQuery(sql);
            //处理结果
            while (resultSet.next()) {
                //取值
                String title = resultSet.getString("title");
                String author = resultSet.getString("author");
                String content = resultSet.getString("content");
                int viewCount = resultSet.getInt("view_count");
                String categoryName = resultSet.getString("name");
                String status = resultSet.getString("status");
                //封装

                blogVO.setId(id);
                blogVO.setTitle(title);
                blogVO.setAuthor(author);
                blogVO.setContent(content);
                blogVO.setViewCount(viewCount);
                blogVO.setCategoryName(categoryName);
                blogVO.setStatus(status);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return blogVO;
    }

    public List<Blog> findAll(){
        List<Blog> list=new ArrayList<>();
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            // url?
            //连接数据库
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/blog", "root", "root");
            //执行sql语句
            Statement statement = conn.createStatement();
            String sql="select `id`,`title` from `blog` where status=1;";
            ResultSet resultSet = statement.executeQuery(sql);
            //取出数据
            while (resultSet.next()) {
                //取值
                String title = resultSet.getString("title");
                int id = resultSet.getInt("id");

                //封装
                Blog blog=new Blog();
                blog.setId(id);
                blog.setTitle(title);
                //加入到list
                list.add(blog);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return list;
    }
}
